package com.youlai.boot.util;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.ThreadLocalRandom;

/**
 * 品牌数据生成器
 * 
 * <p>独立的品牌数据生成工具，不依赖Spring上下文，可直接运行。
 * 高效生成2023年1月1日至2025年6月9日的品牌表现测试数据。</p>
 * 
 * <h3>主要特性:</h3>
 * <ul>
 *   <li>完全独立，无需Spring Boot环境</li>
 *   <li>JDBC批处理，高效数据生成</li>
 *   <li>模拟真实电商品牌数据</li>
 *   <li>包含季节性和趋势性波动</li>
 *   <li>支持多品牌、多类目、多经营模式</li>
 * </ul>
 * 
 * <h3>使用方法:</h3>
 * <pre>
 * // 修改数据库连接信息后直接运行main方法
 * java com.youlai.boot.util.BrandDataGenerator
 * </pre>
 * 
 * @author Jason
 * @since 2025-06-05
 */
public class BrandDataGenerator {

    // 数据库连接配置 - 请根据实际情况修改
    private static final String DB_URL = "jdbc:mysql://localhost:3306/youlai_boot?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true";
    private static final String DB_USERNAME = "root";
    private static final String DB_PASSWORD = "q&sQrH#$3ix7";

    // 批处理大小
    private static final int BATCH_SIZE = 1000;

    // 日期格式化器
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");

    // 品牌配置
    private static final List<String> BRAND_NAMES = Arrays.asList(
            "Nike", "Adidas", "Apple", "Samsung", "华为", "小米", "Zara", "H&M", 
            "优衣库", "欧莱雅", "兰蔻", "雅诗兰黛", "海尔", "美的", "格力", "TCL",
            "IKEA", "无印良品", "宜家", "顾家家居", "全友家居", "索尼", "戴尔", "联想",
            "迪卡侬", "耐克", "阿迪达斯", "安踏", "李宁", "特步"
    );

    // 一级类目配置
    private static final List<String> FIRST_CATEGORIES = Arrays.asList(
            "家居生活", "数码电器", "服装配饰", "美妆护肤", "运动户外"
    );

    // 二级类目配置
    private static final String[][] SECOND_CATEGORIES = {
            {"家具", "家纺", "厨具", "收纳整理", "家装建材"}, // 家居生活
            {"手机数码", "电脑办公", "家用电器", "智能设备", "影音娱乐"}, // 数码电器
            {"女装", "男装", "童装", "鞋靴", "箱包配饰"}, // 服装配饰
            {"面部护肤", "彩妆", "香水", "身体护理", "男士护理"}, // 美妆护肤
            {"运动鞋服", "健身器材", "户外装备", "体育用品", "运动配件"} // 运动户外
    };

    // 三级类目配置
    private static final String[][][] THIRD_CATEGORIES = {
            { // 家居生活
                {"沙发", "床", "衣柜", "餐桌", "书桌"}, // 家具
                {"床上用品", "窗帘", "地毯", "抱枕", "毛巾"}, // 家纺
                {"锅具", "刀具", "餐具", "小家电", "保鲜盒"}, // 厨具
                {"储物箱", "收纳盒", "衣架", "鞋架", "整理袋"}, // 收纳整理
                {"涂料", "地板", "瓷砖", "灯具", "五金"} // 家装建材
            },
            { // 数码电器
                {"智能手机", "平板电脑", "智能手表", "耳机", "充电器"}, // 手机数码
                {"笔记本电脑", "台式机", "显示器", "键盘", "鼠标"}, // 电脑办公
                {"冰箱", "洗衣机", "空调", "电视", "微波炉"}, // 家用电器
                {"智能音箱", "扫地机器人", "智能门锁", "监控摄像头", "智能开关"}, // 智能设备
                {"音响", "投影仪", "游戏机", "VR设备", "蓝牙音箱"} // 影音娱乐
            },
            { // 服装配饰
                {"连衣裙", "上衣", "裤装", "外套", "内衣"}, // 女装
                {"T恤", "衬衫", "裤子", "外套", "内裤"}, // 男装
                {"连体衣", "上衣", "裤子", "外套", "鞋子"}, // 童装
                {"运动鞋", "休闲鞋", "皮鞋", "靴子", "拖鞋"}, // 鞋靴
                {"手提包", "双肩包", "钱包", "手表", "首饰"} // 箱包配饰
            },
            { // 美妆护肤
                {"洁面", "爽肤水", "乳液", "面霜", "面膜"}, // 面部护肤
                {"口红", "粉底", "眼影", "睫毛膏", "腮红"}, // 彩妆
                {"女士香水", "男士香水", "香体喷雾", "固体香膏", "香氛蜡烛"}, // 香水
                {"沐浴露", "身体乳", "护手霜", "防晒霜", "脱毛膏"}, // 身体护理
                {"剃须刀", "剃须膏", "男士洁面", "男士乳液", "男士香水"} // 男士护理
            },
            { // 运动户外
                {"跑步鞋", "篮球鞋", "足球鞋", "运动服", "运动裤"}, // 运动鞋服
                {"跑步机", "哑铃", "瑜伽垫", "健身车", "拉力器"}, // 健身器材
                {"帐篷", "睡袋", "登山包", "冲锋衣", "登山鞋"}, // 户外装备
                {"篮球", "足球", "羽毛球", "乒乓球", "网球"}, // 体育用品
                {"护膝", "护腕", "运动手表", "水壶", "运动毛巾"} // 运动配件
            }
    };

    // 经营模式
    private static final List<String> OPERATION_MODES = Arrays.asList(
            "旗舰店", "专营店", "授权店"
    );

    // 品牌规模因子（模拟不同品牌的规模差异）
    private static final double[] BRAND_SCALE_FACTORS = {
            2.5, 2.2, 3.0, 2.8, 2.1, 1.8, 1.9, 1.7, // Nike, Adidas, Apple, Samsung, 华为, 小米, Zara, H&M
            1.5, 2.0, 1.8, 1.9, 1.6, 1.4, 1.3, 1.2, // 优衣库, 欧莱雅, 兰蔻, 雅诗兰黛, 海尔, 美的, 格力, TCL
            1.7, 1.3, 1.7, 1.1, 1.0, 1.8, 1.5, 1.6, // IKEA, 无印良品, 宜家, 顾家家居, 全友家居, 索尼, 戴尔, 联想
            1.4, 2.5, 2.2, 1.3, 1.2, 1.1  // 迪卡侬, 耐克, 阿迪达斯, 安踏, 李宁, 特步
    };

    // 日期范围
    private static final LocalDate START_DATE = LocalDate.of(2023, 1, 1);
    private static final LocalDate END_DATE = LocalDate.of(2025, 6, 9);

    /**
     * 主方法 - 直接运行数据生成
     */
    public static void main(String[] args) {
        System.out.println("🚀 开始执行品牌数据生成任务...");
        long startTime = System.currentTimeMillis();

        try (Connection conn = getConnection()) {
            BrandDataGenerator generator = new BrandDataGenerator();
            
            // 设置批处理模式
            conn.setAutoCommit(false);
            
            // 清空现有数据
            generator.clearExistingData(conn);
            
            // 生成新数据
            generator.generateBatchData(conn);
            
            // 提交事务
            conn.commit();
            
            // 验证结果
            generator.validateData(conn);
            
            long duration = System.currentTimeMillis() - startTime;
            System.out.println("🎉 数据生成完成！总耗时: " + duration + "ms");
            
        } catch (Exception e) {
            System.err.println("❌ 数据生成失败: " + e.getMessage());
            e.printStackTrace();
        }
        
        System.out.println("✅ 品牌数据生成任务完成！");
    }

    /**
     * 获取数据库连接
     */
    private static Connection getConnection() throws SQLException {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new SQLException("MySQL驱动未找到", e);
        }
        
        return DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
    }

    /**
     * 清空现有数据
     */
    private void clearExistingData(Connection conn) throws SQLException {
        System.out.println("🧹 正在清空现有品牌数据...");
        
        try (Statement stmt = conn.createStatement()) {
            int deletedRows = stmt.executeUpdate("DELETE FROM brand WHERE is_deleted = 0");
            System.out.println("✅ 已删除 " + deletedRows + " 条现有品牌记录");
            
            // 重置自增ID
            try {
                stmt.execute("ALTER TABLE brand AUTO_INCREMENT = 1");
                System.out.println("✅ 已重置品牌表的自增ID");
            } catch (SQLException e) {
                System.out.println("⚠️ 重置自增ID失败，但不影响数据生成: " + e.getMessage());
            }
        }
    }

    /**
     * 批量生成数据
     */
    private void generateBatchData(Connection conn) throws SQLException {
        System.out.println("📊 开始生成品牌表现数据...");
        System.out.println("📅 数据范围: " + START_DATE + " 至 " + END_DATE);
        System.out.println("🏷️ 品牌数量: " + BRAND_NAMES.size());
        System.out.println("📂 类目数量: " + FIRST_CATEGORIES.size());
        
        // 计算总记录数（每个品牌每月生成3-5条记录，模拟不同类目组合）
        long totalDays = START_DATE.until(END_DATE).getDays() + 1;
        long totalMonths = totalDays / 30; // 大概估算月份数
        long estimatedRecords = BRAND_NAMES.size() * totalMonths * 4; // 平均每品牌每月4条
        System.out.println("📈 预计生成记录数: " + estimatedRecords + " 条");

        String insertSql = """
            INSERT INTO brand (
                time_period, brand_name, first_category, second_category, third_category,
                operation_mode, page_views, visitor_count, avg_page_views_per_visitor,
                avg_stay_time, transaction_user_count, conversion_rate, transaction_order_count,
                transaction_item_count, transaction_amount, average_transaction_value,
                create_time, update_time, is_deleted
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), 0)
            """;

        try (PreparedStatement pstmt = conn.prepareStatement(insertSql)) {
            int batchCount = 0;
            long processedRecords = 0;

            // 遍历每一天，但不是每天都生成，而是按月/周生成
            for (LocalDate currentDate = START_DATE; !currentDate.isAfter(END_DATE); currentDate = currentDate.plusDays(7)) {
                // 为每个品牌生成多个类目组合的数据
                for (int brandIndex = 0; brandIndex < BRAND_NAMES.size(); brandIndex++) {
                    // 每个品牌生成2-4个不同类目的记录
                    int recordsPerBrand = 2 + ThreadLocalRandom.current().nextInt(3);
                    
                    for (int i = 0; i < recordsPerBrand; i++) {
                        setRecordParameters(pstmt, currentDate, brandIndex);
                        pstmt.addBatch();
                        batchCount++;

                        // 当批次达到指定大小时，执行批量插入
                        if (batchCount >= BATCH_SIZE) {
                            pstmt.executeBatch();
                            conn.commit();
                            processedRecords += batchCount;
                            
                            // 输出进度
                            if (processedRecords % (BATCH_SIZE * 10) == 0) {
                                double progress = (double) processedRecords / estimatedRecords * 100;
                                System.out.println("⏳ 已处理: " + processedRecords + " 条记录 (" + String.format("%.1f", progress) + "%)");
                            }
                            
                            batchCount = 0;
                        }
                    }
                }
            }

            // 处理剩余记录
            if (batchCount > 0) {
                pstmt.executeBatch();
                conn.commit();
                processedRecords += batchCount;
            }

            System.out.println("✅ 品牌数据生成完成，共处理 " + processedRecords + " 条记录");
        }
    }

    /**
     * 设置单条记录的参数
     */
    private void setRecordParameters(PreparedStatement pstmt, LocalDate reportDate, int brandIndex) throws SQLException {
        String brandName = BRAND_NAMES.get(brandIndex);
        double scaleFactor = BRAND_SCALE_FACTORS[brandIndex];
        
        // 随机选择类目组合
        int firstCategoryIndex = ThreadLocalRandom.current().nextInt(FIRST_CATEGORIES.size());
        String firstCategory = FIRST_CATEGORIES.get(firstCategoryIndex);
        
        int secondCategoryIndex = ThreadLocalRandom.current().nextInt(SECOND_CATEGORIES[firstCategoryIndex].length);
        String secondCategory = SECOND_CATEGORIES[firstCategoryIndex][secondCategoryIndex];
        
        int thirdCategoryIndex = ThreadLocalRandom.current().nextInt(THIRD_CATEGORIES[firstCategoryIndex][secondCategoryIndex].length);
        String thirdCategory = THIRD_CATEGORIES[firstCategoryIndex][secondCategoryIndex][thirdCategoryIndex];
        
        // 随机选择经营模式
        String operationMode = OPERATION_MODES.get(ThreadLocalRandom.current().nextInt(OPERATION_MODES.size()));
        
        // 计算波动因子
        double seasonalFactor = calculateSeasonalFactor(reportDate);
        double trendFactor = calculateTrendFactor(reportDate);
        double categoryFactor = getCategoryFactor(firstCategory);
        double randomFactor = 0.7 + ThreadLocalRandom.current().nextDouble() * 0.6;
        double combinedFactor = scaleFactor * seasonalFactor * trendFactor * categoryFactor * randomFactor;
        
        // 生成浏览数据
        int basePageViews = (int) (50000 * combinedFactor);
        int pageViews = Math.max(10000, basePageViews + ThreadLocalRandom.current().nextInt(-10000, 20000));
        
        int baseVisitorCount = (int) (pageViews * (0.3 + ThreadLocalRandom.current().nextDouble() * 0.2));
        int visitorCount = Math.max(5000, baseVisitorCount);
        
        BigDecimal avgPageViewsPerVisitor = BigDecimal.valueOf((double) pageViews / visitorCount)
                .setScale(2, RoundingMode.HALF_UP);
        
        // 平均停留时长（秒）
        int avgStayTime = 120 + ThreadLocalRandom.current().nextInt(240);
        
        // 成交数据
        double conversionRateDouble = 0.02 + ThreadLocalRandom.current().nextDouble() * 0.08; // 2%-10%
        BigDecimal conversionRate = BigDecimal.valueOf(conversionRateDouble * 100).setScale(4, RoundingMode.HALF_UP);
        
        int transactionUserCount = (int) (visitorCount * conversionRateDouble);
        transactionUserCount = Math.max(100, transactionUserCount);
        
        // 订单和件数
        int transactionOrderCount = (int) (transactionUserCount * (1.1 + ThreadLocalRandom.current().nextDouble() * 0.3));
        int transactionItemCount = (int) (transactionOrderCount * (1.5 + ThreadLocalRandom.current().nextDouble() * 2.0));
        
        // 金额计算（以分为单位存储）
        double avgOrderValue = getAvgOrderValueByCategory(firstCategory) * (0.8 + ThreadLocalRandom.current().nextDouble() * 0.4);
        long transactionAmountCents = (long) (transactionOrderCount * avgOrderValue * 100); // 转换为分
        BigDecimal transactionAmount = BigDecimal.valueOf(transactionAmountCents);
        
        // 客单价（以分为单位）
        long avgTransactionValueCents = transactionAmountCents / transactionUserCount;
        BigDecimal averageTransactionValue = BigDecimal.valueOf(avgTransactionValueCents);
        
        // 时间周期
        String timePeriod = reportDate.format(DateTimeFormatter.ofPattern("yyyy-MM"));
        
        // 设置参数
        pstmt.setString(1, timePeriod);
        pstmt.setString(2, brandName);
        pstmt.setString(3, firstCategory);
        pstmt.setString(4, secondCategory);
        pstmt.setString(5, thirdCategory);
        pstmt.setString(6, operationMode);
        pstmt.setInt(7, pageViews);
        pstmt.setInt(8, visitorCount);
        pstmt.setBigDecimal(9, avgPageViewsPerVisitor);
        pstmt.setInt(10, avgStayTime);
        pstmt.setInt(11, transactionUserCount);
        pstmt.setBigDecimal(12, conversionRate);
        pstmt.setInt(13, transactionOrderCount);
        pstmt.setInt(14, transactionItemCount);
        pstmt.setBigDecimal(15, transactionAmount);
        pstmt.setBigDecimal(16, averageTransactionValue);
    }

    /**
     * 计算季节性因子
     */
    private double calculateSeasonalFactor(LocalDate date) {
        int month = date.getMonthValue();
        
        if (month <= 2) return 1.3;           // 春节期间
        else if (month == 3) return 0.9;      // 春节后回落
        else if (month == 6) return 1.4;      // 618购物节
        else if (month == 11) return 1.5;     // 双11购物节
        else if (month == 12) return 1.2;     // 年末促销
        else return 0.9 + ThreadLocalRandom.current().nextDouble() * 0.2; // 其他月份
    }

    /**
     * 计算年度趋势因子
     */
    private double calculateTrendFactor(LocalDate date) {
        int year = date.getYear();
        return switch (year) {
            case 2023 -> 0.95;  // 基准年稍低
            case 2024 -> 1.08;  // 8%增长
            case 2025 -> 1.18;  // 18%增长
            default -> 1.0;
        };
    }

    /**
     * 根据类目获取类目因子
     */
    private double getCategoryFactor(String firstCategory) {
        return switch (firstCategory) {
            case "数码电器" -> 1.3;      // 数码电器类目较火爆
            case "服装配饰" -> 1.2;      // 服装类目热门
            case "美妆护肤" -> 1.1;      // 美妆类目稳定增长
            case "运动户外" -> 1.0;      // 运动户外中等
            case "家居生活" -> 0.9;      // 家居生活相对较慢
            default -> 1.0;
        };
    }

    /**
     * 根据类目获取平均订单价值
     */
    private double getAvgOrderValueByCategory(String firstCategory) {
        return switch (firstCategory) {
            case "数码电器" -> 1500.0;   // 数码电器客单价高
            case "家居生活" -> 800.0;    // 家居生活中等客单价
            case "服装配饰" -> 300.0;    // 服装类目客单价中低
            case "美妆护肤" -> 200.0;    // 美妆护肤客单价较低
            case "运动户外" -> 400.0;    // 运动户外中等客单价
            default -> 500.0;
        };
    }

    /**
     * 验证生成的数据
     */
    private void validateData(Connection conn) throws SQLException {
        System.out.println("🔍 正在验证生成的品牌数据...");
        
        try (Statement stmt = conn.createStatement()) {
            // 检查总记录数
            ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM brand WHERE is_deleted = 0");
            rs.next();
            int totalCount = rs.getInt(1);
            
            // 检查时间范围
            rs = stmt.executeQuery("SELECT MIN(time_period), MAX(time_period) FROM brand WHERE is_deleted = 0");
            rs.next();
            String minPeriod = rs.getString(1);
            String maxPeriod = rs.getString(2);
            
            // 检查品牌数量
            rs = stmt.executeQuery("SELECT COUNT(DISTINCT brand_name) FROM brand WHERE is_deleted = 0");
            rs.next();
            int brandCount = rs.getInt(1);
            
            // 检查类目数量
            rs = stmt.executeQuery("SELECT COUNT(DISTINCT first_category) FROM brand WHERE is_deleted = 0");
            rs.next();
            int categoryCount = rs.getInt(1);
            
            // 检查经营模式数量
            rs = stmt.executeQuery("SELECT COUNT(DISTINCT operation_mode) FROM brand WHERE is_deleted = 0");
            rs.next();
            int operationModeCount = rs.getInt(1);
            
            // 检查数据合理性
            rs = stmt.executeQuery("SELECT AVG(conversion_rate), MIN(conversion_rate), MAX(conversion_rate) FROM brand WHERE is_deleted = 0");
            rs.next();
            double avgConversionRate = rs.getDouble(1);
            double minConversionRate = rs.getDouble(2);
            double maxConversionRate = rs.getDouble(3);
            
            System.out.println("📊 品牌数据验证结果:");
            System.out.println("   总记录数: " + totalCount);
            System.out.println("   时间范围: " + minPeriod + " 至 " + maxPeriod);
            System.out.println("   品牌数量: " + brandCount + "/" + BRAND_NAMES.size());
            System.out.println("   类目数量: " + categoryCount + "/" + FIRST_CATEGORIES.size());
            System.out.println("   经营模式数量: " + operationModeCount + "/" + OPERATION_MODES.size());
            System.out.println("   转化率范围: " + String.format("%.2f%% - %.2f%% (平均: %.2f%%)", 
                minConversionRate, maxConversionRate, avgConversionRate));
            
            if (totalCount > 5000 && brandCount >= 25 && categoryCount >= 5 && operationModeCount >= 3) {
                System.out.println("✅ 品牌数据验证通过");
            } else {
                System.out.println("❌ 品牌数据验证失败");
            }
        }
    }
} 